import pandas as pd
import pymysql
from fuzzywuzzy import process

# 假设你的DataFrame叫df，你要搜索的列名叫'column_name'，你要搜索的词是'your_word'

# 读取Excel文件
df = pd.read_excel(r'5000词.xlsx', header=None)

conn = pymysql.connect(host='localhost', port=3306, password='abc123', user='root', db="db_word")

cursor = conn.cursor()

sql = """
    SELECT id,word FROM word
"""
choices_ = df[0].dropna().unique()  # 假设我们只对唯一值感兴趣
cursor.execute(sql)
results = cursor.fetchall()
# print(results)
for d1, d2 in [(_[0], _[1]) for _ in results]:
    # 筛查
    # 在指定列中找到与your_word最相似的3个词，不包括your_word本身
    your_word = d2
    choices = [word for word in choices_ if word != your_word and len(word) >= len(your_word)]  # 移除要搜索的单词
    closest_matches = process.extract(your_word, choices, limit=3)
    sql_ = """
        INSERT INTO looklike (word, word_id) VALUES (%s,%s)
    """
    for index,item in enumerate(closest_matches):
        values = (item[0], d1)
        cursor.execute(sql_, values)
        conn.commit()
